﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Management.Automation;
using System.Data;
using MySql.Data.MySqlClient;
using System.Management.Automation.Runspaces;
using System.Collections.ObjectModel;

namespace _3TSolutions.MySqlPSExtensions
{
    /// <summary>
    /// Invokes MySQL commands on local or remote MySql Server instances
    /// </summary>
    [Cmdlet("Invoke", "MySqlCmd")]
    [CmdletBinding()]
    public class MySqlCmd : Cmdlet
    {
        public override string ToString()
        {
            return "Invoke-MySqlCmd";
        }

        protected override void ProcessRecord()
        {
            DataTable resultTable = new DataTable();
            try
            {
                string connectionString = string.Format("Server={0};database={1};port={4};Uid={2};Pwd={3};", InstanceName, DatabaseName, Username, Password, Port);
                MySqlConnection connection = new MySqlConnection(connectionString);
                connection.Open();
                MySqlCommand command = new MySqlCommand(Query, connection);
                if (Query.ToLower().Contains("select"))
                {

                    MySqlDataAdapter adapter = new MySqlDataAdapter(command);
                    adapter.Fill(resultTable);
                    IList<DataRow> rows = new List<DataRow>();
                    foreach (DataRow item in resultTable.Rows)
                    {
                        rows.Add(item);
                    }
                    WriteObject(rows);

                }
                else
                {
                    command.ExecuteNonQuery();
                    WriteVerbose("Command executed successfully !");
                }
                connection.Close();
            }
            catch (Exception e)
            {
                WriteError(new ErrorRecord(e, e.Message, ErrorCategory.WriteError, resultTable));
            }
        }

        private string _instanceName;
        /// <summary>
        /// MySql Server Instance Name
        /// </summary>
        [Parameter(Position = 0, Mandatory = true, ValueFromPipeline = false, ValueFromPipelineByPropertyName = false, HelpMessage = "Mysql Server Instance Name")]
        [Alias("Instance")]
        public string InstanceName
        {
            get
            {
                return _instanceName;
            }
            set
            {
                _instanceName = value;
            }
        }

        private string _databaseName;
        /// <summary>
        /// MySql Database Name
        /// </summary>
        [Parameter(Position = 1, Mandatory = true, ValueFromPipeline = true, ValueFromPipelineByPropertyName = true, HelpMessage = "MySql database name")]
        [Alias("Database")]
        public string DatabaseName
        {
            get
            {
                return _databaseName;
            }
            set
            {
                _databaseName = value;
            }
        }

        private string _query;
        /// <summary>
        /// MySql Query
        /// </summary>
        [Parameter(Position = 2, Mandatory = true, ValueFromPipeline = true, ValueFromPipelineByPropertyName = true, HelpMessage = "Sql Command")]
        public string Query
        {
            get
            {
                return _query;
            }
            set
            {
                _query = value;
            }
        }

        private string _userName;
        /// <summary>
        /// MySql Credentials Username
        /// </summary>
        [Parameter(Position = 3, Mandatory = true, ValueFromPipeline = true, ValueFromPipelineByPropertyName = true, HelpMessage = "Username credentials for Securtiy access")]
        public string Username
        {
            get
            {
                return _userName;
            }
            set
            {
                _userName = value;
            }
        }

        private string _password;
        /// <summary>
        /// MySql Credentials Password
        /// </summary>
        [Parameter(Position = 4, Mandatory = false, ValueFromPipeline = true, ValueFromPipelineByPropertyName = true, HelpMessage = "Username credentials for Securtiy access")]
        public string Password
        {
            get
            {
                return _password;
            }
            set
            {
                _password = value;
            }
        }

        private int _port = 3306;
        /// <summary>
        /// MySql Port Number
        /// </summary>
        [Parameter(Position = 5, Mandatory = false, ValueFromPipeline = true, ValueFromPipelineByPropertyName = true, HelpMessage = "TCP/IP port number for th database remote access")]
        public int Port
        {
            get
            {
                return _port;
            }
            set
            {
                _port = value;
            }
        }

    }
}
